package com.ctshk.rpc.train.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.ctshk.rpc.train.dto.TrainNumberListDTO;
import com.ctshk.rpc.train.entity.TrainNumber;
import com.ctshk.rpc.train.req.TrainNumberPageReq;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

/**
 * <p>
 * 火车车次表 Mapper 接口
 * </p>
 *
 * @author 彭柳霖
 * @since 2021-03-10
 */
public interface TrainNumberMapper extends BaseMapper<TrainNumber> {


    /**
     * 获取火车车次列表
     * @param req
     * @return
     */
    @Select("<script>"+"select t1.id,t1.railway_corporation_name, \n" +
            "t1.type_name,t1.train_number_name, \n" +
            "GROUP_CONCAT(distinct t2.seat_level_name) as seatLevels, \n" +
            "GROUP_CONCAT(distinct t3.seat_number) as seatNumbers, \n" +
            "t1.departure_city_name,t1.arrive_city_name, \n" +
            "GROUP_CONCAT(distinct t4.station_name order by t4.id) as stationNames, \n" +
            "t1.departure_time,t1.terminus_time,t1.status,  \n" +
            "t1.child_age_begin,t1.child_age_end, \n" +
            "t1.adult_age_begin,t1.adult_age_end,t1.old_age_begin \n" +
            "from t_train_number t1 \n" +
            "left join t_train_number_seat_level t2 on t2.train_number_id  = t1.id \n" +
            "left join t_train_number_seat t3 on t3.train_number_id  = t1.id \n" +
            "left join t_train_stop_station t4 on t4.train_number_id  = t1.id \n" +
            "WHERE t1.is_deleted = 0 <if test=\"req.railwayCorporationName != null and req.railwayCorporationName != ''\">AND t1.railway_corporation_name LIKE CONCAT('%',#{req.railwayCorporationName},'%')</if> \n" +
            "<if test=\"req.trainNumberName != null and req.trainNumberName != ''\">AND t1.train_number_name = #{req.trainNumberName}</if> \n" +
            "<if test=\"req.stationName != null and req.stationName != ''\">AND t4.station_name LIKE CONCAT('%',#{req.stationName},'%')</if> \n" +
            "<if test=\"req.status != null and req.status != 0\">AND t1.status = #{req.status}</if> \n" +
            "GROUP BY t1.id order by t1.id desc"+"</script>")
    IPage<TrainNumberListDTO> queryList(@Param("req") TrainNumberPageReq req, IPage<TrainNumberListDTO> agentPage);


}
